﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using vudu.ClasesDominio;

namespace vudu.Datos.SqlServer
{
       /// <summary> Esta clase contiene gestionar escuela : ingresar, consultar, modificar, eliminar escuela
       /// en la base de datos
       /// </summary>
      
    public class SqlServerEscuela
    {

        #region Insertar Escuela

        /// <summary>
        /// Funcion que llama al stored procedure de la base de datos para insertar una nueva escuela
        /// </summary>
        /// <returns>
        /// un int con la primary key de la escuela insertada o 0 si no se inserto
        /// </returns>
        public int InsertarEscuela(Escuela escuela, string _nombreUniversidad)
        {   
            //Se instancia un objeto de tipo Conexion y un objeto de tipo SqlCommand, para hacer la conexion a la base de datos 
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();

            
            try
            {   
                //Se abre la conexion a la base de datos
                miConexion.AbrirConexionBd(); 
                //se introducen los datos de la conexion y el nombre del stored procedure a utilizar
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[InsertarEscuela]";
                command.CommandTimeout = 10;

                //Se introducen los parametros del stored procedure
                command.Parameters.AddWithValue("@fk_Universidad", _nombreUniversidad);
                command.Parameters.AddWithValue("@nombre", escuela.NombreEscuela);
                command.Parameters.AddWithValue("@descripcion", escuela.DescripcionEscuela);
                command.Parameters.AddWithValue("@telefono", escuela.TelefonoEscuela);
                command.Parameters.AddWithValue("@director", escuela.DirectorEscuela);
                command.Parameters.AddWithValue("@direccion",escuela.DireccionEscuela);
                command.Parameters.AddWithValue("@estado", escuela.EstadoEscuela);
                command.Parameters.AddWithValue("@escuelaInsertada", SqlDbType.Int);
                command.Parameters.AddWithValue("@validacion", SqlDbType.Int);

                command.Parameters["@fk_Universidad"].Direction = ParameterDirection.Input;
                command.Parameters["@nombre"].Direction = ParameterDirection.Input;
                command.Parameters["@descripcion"].Direction = ParameterDirection.Input;
                command.Parameters["@telefono"].Direction = ParameterDirection.Input;
                command.Parameters["@director"].Direction = ParameterDirection.Input;
                command.Parameters["@direccion"].Direction = ParameterDirection.Input;
                command.Parameters["@estado"].Direction = ParameterDirection.Input;
                command.Parameters["@escuelaInsertada"].Direction = ParameterDirection.Output;
                command.Parameters["@validacion"].Direction = ParameterDirection.Output;

                // se ejecuta el comando, se ejecuta el stored procedure InsertarEscuela
                command.ExecuteReader();
                
                //se obtiene el parametro de salida que es el id de la escuela insertada
                int escuelaInsertada = (int)command.Parameters["@escuelaInsertada"].Value;

                return escuelaInsertada;
            }
            catch (SqlException)
            {
                return 0;
            }
            catch (Exception)
            {
                return 0;
            }
            
             //se cierra la conexion una vez ejecutado el comando.
            finally
            {
                miConexion.CerrarConexionBd();
            }
        }

        #endregion Final de la funcion InsertarEscuela

        #region Obtener Universidad Profesor

        public String ObtenerUniversidadProfesor(int idProfesor)
        {
            //  instancia de un objeto de tipo conexion para acceder a la bd
            //  instancia de un objeto de tipo sqlCommand 
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            String _universidad = "";
            try
            {
                //  se abre la conexion a bd vudu
                miConexion.AbrirConexionBd();
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[ObtenerUniversidadProfesor]";
                command.CommandTimeout = 10;
                
                //  variables del stored procedure de sql server.
                command.Parameters.AddWithValue("@idProfesor", idProfesor);
               // Se indica que es un parametro de entrada
                command.Parameters["@idProfesor"].Direction = ParameterDirection.Input;
               
                //se carga la informacion consultada en una variable
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                  _universidad = reader.GetString(0);
                 
                }
                return _universidad;
            }
            catch (SqlException)
            {
                return null;
            }
            catch (Exception)
            {
                return null;
            }
            //se cierra la conexion independientemente de que se haya detectado o no una excepcion.
            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }
        #endregion Final de Obtener Universidad Profesor

        #region Obtener Universidad Estudiante

        public String ObtenerUniversidadEstudiante(int idEstudiante)
        {
            //  instancia de un objeto de tipo conexion para acceder a la bd
            //  instancia de un objeto de tipo sqlCommand 
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            String _universidad = "";
            try
            {
                //  se abre la conexion a bd vudu
                miConexion.AbrirConexionBd();
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[ObtenerUniversidadEstudiante]";
                command.CommandTimeout = 10;

                //  variables del stored procedure de sql server.
                command.Parameters.AddWithValue("@idEstudiante", idEstudiante);
                // Se indica que es un parametro de entrada
                command.Parameters["@idEstudiante"].Direction = ParameterDirection.Input;

                //se carga la informacion consultada en una variable
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    _universidad = reader.GetString(0);

                }
                return _universidad;
            }
            catch (SqlException)
            {
                return null;
            }
            catch (Exception)
            {
                return null;
            }
            //se cierra la conexion independientemente de que se haya detectado o no una excepcion.
            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }
        #endregion Final de Obtener Universidad Estudiante

        #region Consultar una escuela

        public Escuela ConsultarEscuela(int idEscuela)
        {
            //  instancia de un objeto de tipo conexion para acceder a la bd
            //  instancia de un objeto de tipo sqlCommand 
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            try
            {
                //  se abre la conexion a bd vudu
                miConexion.AbrirConexionBd();
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[ConsultarEscuela]";
                command.CommandTimeout = 10;

                //  variables del stored procedure de sql server.
                command.Parameters.AddWithValue("@idEscuela", idEscuela);
                // command.Parameters.AddWithValue("@nombreUniversidad",universidad);
                // Se indica que es un parametro de entrada
                command.Parameters["@idEscuela"].Direction = ParameterDirection.Input;
                // command.Parameters["@nombreUniversidad"].Direction = ParameterDirection.Input;

                //se carga la informacion consultada en un objeto de tipo Escuela
                Escuela escuelaConsultada = new Escuela();
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    escuelaConsultada.IdEscuela = reader.GetInt32(0);
                    escuelaConsultada.NombreEscuela = reader.GetString(1);
                    escuelaConsultada.DescripcionEscuela = reader.GetString(2);
                    escuelaConsultada.TelefonoEscuela = reader.GetString(3);
                    escuelaConsultada.DirectorEscuela = reader.GetString(4);
                    escuelaConsultada.DireccionEscuela = reader.GetString(5);
                    escuelaConsultada.EstadoEscuela = reader.GetInt32(6);


                }
                return escuelaConsultada;
            }
            catch (SqlException)
            {
                return null;
            }
            catch (Exception)
            {
                return null;
            }
            //se cierra la conexion independientemente de que se haya detectado o no una excepcion.
            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }
        #endregion Final de Consultar Escuela

        #region Consultar Lista de Escuela por Universidad

        public List<Escuela> SeleccionListaEscuela(string nombreUniversidad)
        {
       
            {
                
                // se instancia un objeto de tipo conexion y otro de tipo sqlComand
                Conexion miConexion = new Conexion();
                SqlCommand command = new SqlCommand();
                SqlDataReader reader = null;
                List<Escuela> listaEscuela = new List<Escuela>();
                         
                try
                {
                    //se abre una conexion a la bd vudu
                    miConexion.AbrirConexionBd();
                    command.Connection = miConexion.ObjetoConexion();
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    // Se indica el nombre del store procedure
                    command.CommandText = "[dbo].[ConsultarListaEscuela]";
                    command.CommandTimeout = 10;

                    //Se introducen los parametros del stored procedure
                    command.Parameters.AddWithValue("@nombreUniversidad", nombreUniversidad);
                    //Se indica que es un parametro de entrada
                    command.Parameters["@nombreUniversidad"].Direction = ParameterDirection.Input;
                    
                    reader = command.ExecuteReader();
                    // guarda registro a registro cada objeto de tipo escuela en la lista
                    while (reader.Read())
                    {
                        
                        Escuela escuela = new Escuela();
                        escuela.IdEscuela = reader.GetInt32(0);
                        escuela.NombreEscuela = reader.GetString(1);
                        escuela.DescripcionEscuela = reader.GetString(2);
                        escuela.TelefonoEscuela = reader.GetString(3);
                        escuela.DirectorEscuela = reader.GetString(4);
                        escuela.DireccionEscuela = reader.GetString(5);
                        escuela.EstadoEscuela = reader.GetInt32(6);
                        
                        listaEscuela.Add(escuela);          
                        
                    }
                    
                    return listaEscuela;
                }
                catch (SqlException)
                {
                    return null;
                }
                catch (Exception)
                {
                    return null;
                }
                //se cierra la conexion independientemente de que se haya detectado o no una excepcion.
                finally
                {
                    if (reader != null)
                        reader.Close();
                    miConexion.CerrarConexionBd();
                }
            }
        }

        #endregion Final de Lista de Escuelas por universidad

        #region Consultar Pensum Activo de una Escuela
        //Obtengo el id del pensum activo
        public List<int> ConsultarIdPensumActivos(String estatus ,int idEscuela)
        {
            //  instancia de un objeto de tipo conexion para acceder a la bd
            //  instancia de un objeto de tipo sqlCommand 
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            try
            {
                //  se abre la conexion a bd vudu
                miConexion.AbrirConexionBd();
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[ConsultarIdPensumActivos]";
                command.CommandTimeout = 10;

                //  variables del stored procedure de sql server.
                command.Parameters.AddWithValue("@Estatus", estatus);
                command.Parameters.AddWithValue("@Id",idEscuela);
                // Se indica que es un parametro de entrada
                command.Parameters["@Estatus"].Direction = ParameterDirection.Input;
                command.Parameters["@Id"].Direction = ParameterDirection.Input;

                //se carga la informacion consultada en un objeto de tipo Escuela
                List<int> listaPensum = new List<int>();
                reader = command.ExecuteReader();
                while (reader.Read())
                {

                    listaPensum.Add(reader.GetInt32(0));


                }
                return listaPensum;
            }
            catch (SqlException)
            {
                return null;
            }
            catch (Exception)
            {
                return null;
            }
            //se cierra la conexion independientemente de que se haya detectado o no una excepcion.
            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }
        #endregion Final de Consultar Pensum Activo de una Escuela

        #region Consultar Materias Activas de una Escuela
        //obtengo una lista de id de materias activas
        public List<int> ConsultarIdMateriasActivas(String estatus, int idEscuela)
        {
            //  instancia de un objeto de tipo conexion para acceder a la bd
            //  instancia de un objeto de tipo sqlCommand 
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            try
            {
                //  se abre la conexion a bd vudu
                miConexion.AbrirConexionBd();
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[ConsultarIdMateriasActivas]";
                command.CommandTimeout = 10;

                //  variables del stored procedure de sql server.
                command.Parameters.AddWithValue("@Estatus", estatus);
                command.Parameters.AddWithValue("@Id", idEscuela);
                // Se indica que es un parametro de entrada
                command.Parameters["@Estatus"].Direction = ParameterDirection.Input;
                command.Parameters["@Id"].Direction = ParameterDirection.Input;

                //se carga la informacion consultada en un objeto de tipo Escuela
                List<int> listaMaterias = new List<int>();
                reader = command.ExecuteReader();
                while (reader.Read())
                {

                    listaMaterias.Add(reader.GetInt32(0));


                }
                return listaMaterias;
            }
            catch (SqlException)
            {
                return null;
            }
            catch (Exception)
            {
                return null;
            }
            //se cierra la conexion independientemente de que se haya detectado o no una excepcion.
            finally
            {
                if (reader != null)
                    reader.Close();
                miConexion.CerrarConexionBd();
            }
        }
        #endregion Final de Consultar Materias Activas de una Escuela

        #region Inicio Eliminar Escuela
        public bool EliminarEscuela(int idEscuela)
        {
            //se instancia un objeto de tipo conexion y sqlCommand
            //estos se utilizan para acceder a base de datos y ejecutar el stored procedure en sql server
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            try
            {
                //se abre una conexion a base de datos
                miConexion.AbrirConexionBd();
                // se completa el objeto comando, con la informacion de la conexion , variables, y nombre del stored procedure.
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[EliminarEscuela]";
                command.CommandTimeout = 10;
                //variables del stored procedure de sql server.
                command.Parameters.AddWithValue("@idEscuela", idEscuela);
                command.Parameters["@idEscuela"].Direction = ParameterDirection.Input;
                //se ejecuta el comando
                command.ExecuteReader();
                return true;
            }
            catch (SqlException)
            {
                return false;
            }
            catch (Exception)
            {
                return false;
            }
            //se cierra la conexion independientemente de que se haya detectado o no una excepcion.
            finally
            {
                miConexion.CerrarConexionBd();
            }
        }

        #endregion Fin de Eliminar Escuela
        
        #region Modificar Escuela
        public int ModificarEscuela(Escuela escuela, String universidad)
        {
             // se instancian conexion y sqlcomand para poder abrir la conexion
            Conexion miConexion = new Conexion();
            SqlCommand command = new SqlCommand();
            try
            {
                //Se abre la conexion a la base de datos
                miConexion.AbrirConexionBd();
                //se introducen los datos de la conexion y el nombre del stored procedure a utilizar
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[ModificarEscuela]";
                command.CommandTimeout = 10;

                //Se introducen los parametros del stored procedure
                command.Parameters.AddWithValue("@fk_Universidad", universidad);
                command.Parameters.AddWithValue("@id_escuela", escuela.IdEscuela);
                command.Parameters.AddWithValue("@nombre", escuela.NombreEscuela);
                command.Parameters.AddWithValue("@descripcion", escuela.DescripcionEscuela);
                command.Parameters.AddWithValue("@telefono", escuela.TelefonoEscuela);
                command.Parameters.AddWithValue("@director", escuela.DirectorEscuela);
                command.Parameters.AddWithValue("@direccion", escuela.DireccionEscuela);
                command.Parameters.AddWithValue("@estado", escuela.EstadoEscuela);
                command.Parameters.AddWithValue("@escuelaInsertada", SqlDbType.Int);
                command.Parameters.AddWithValue("@validacion", SqlDbType.Int);
                command.Parameters.AddWithValue("@escuelaDentro", SqlDbType.Int);

                command.Parameters["@fk_Universidad"].Direction = ParameterDirection.Input;
                command.Parameters["@id_escuela"].Direction = ParameterDirection.Input;
                command.Parameters["@nombre"].Direction = ParameterDirection.Input;
                command.Parameters["@descripcion"].Direction = ParameterDirection.Input;
                command.Parameters["@telefono"].Direction = ParameterDirection.Input;
                command.Parameters["@director"].Direction = ParameterDirection.Input;
                command.Parameters["@direccion"].Direction = ParameterDirection.Input;
                command.Parameters["@estado"].Direction = ParameterDirection.Input;
                command.Parameters["@escuelaInsertada"].Direction = ParameterDirection.Output;
                command.Parameters["@validacion"].Direction = ParameterDirection.Output;
                command.Parameters["@escuelaDentro"].Direction = ParameterDirection.Output;

                // se ejecuta el comando, se ejecuta el stored procedure InsertarEscuela
                command.ExecuteReader();

                //se obtiene el parametro de salida que es el id de la escuela insertada
                int escuelaInsertada = (int)command.Parameters["@escuelaInsertada"].Value;

                return escuelaInsertada;
            }
            catch (SqlException)
            {
                return 0;
            }
            catch (Exception)
            {
                return 0;
            }
            // se cierra la conexion independientemente si fue exitosa o no la eliminacion.
            finally
            {
                miConexion.CerrarConexionBd();
            }
        }
        #endregion Fin de Modificar Escuela

        #region Metodo Grupo 3: Consultar Escuelas de miUniversidad

        public List<Escuela> ConsultarEscuelasVecinas(int idEscuela)
        {
            SqlCommand command = new SqlCommand();
            List<Escuela> lista = new List<Escuela>();
            Conexion miConexion = new Conexion();
            try
            {
                miConexion.AbrirConexionBd();
                command.Connection = miConexion.ObjetoConexion();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[SeleccionarEscuelasVecinas]";
                command.CommandTimeout = 10;
                command.Parameters.AddWithValue("@idEscuela", idEscuela);
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Escuela esc = new Escuela();
                    esc.IdEscuela = reader.GetInt32(0);
                    esc.NombreEscuela = reader["nombre"].ToString();
                    lista.Add(esc);
                }
                reader.Close();
            }
            catch (Exception)
            {
            }
            finally
            {
                miConexion.CerrarConexionBd();
            }

            return lista;
        }
        #endregion

    }
}